﻿namespace Hidistro.SqlDal.Comments
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities;
    using Hidistro.Entities.Comments;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public class LeaveCommentDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        private string BuildLeaveCommentQuery(LeaveCommentQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT l.LeaveId FROM Hishop_LeaveComments l where 0=0");
            if (query.MessageStatus == MessageStatus.Replied)
            {
                builder.Append(" and (select Count(ReplyId) from Hishop_LeaveCommentReplys where LeaveId=l.LeaveId) >0 ");
            }
            if (query.MessageStatus == MessageStatus.NoReply)
            {
                builder.Append(" and (select Count(ReplyId) from Hishop_LeaveCommentReplys where LeaveId=l.LeaveId) <=0 ");
            }
            if (!string.IsNullOrEmpty(query.SortBy))
            {
                builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
            }
            else
            {
                builder.Append(" ORDER BY LastDate desc");
            }
            return builder.ToString();
        }

        public bool DeleteLeaveComment(long leaveId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_LeaveCommentReplys WHERE LeaveId=@LeaveId;DELETE FROM Hishop_LeaveComments WHERE LeaveId=@LeaveId");
            this.database.AddInParameter(sqlStringCommand, "leaveId", DbType.Int64, leaveId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool DeleteLeaveCommentReply(long leaveReplyId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_LeaveCommentReplys WHERE replyId=@replyId;");
            this.database.AddInParameter(sqlStringCommand, "replyId", DbType.Int64, leaveReplyId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public LeaveCommentInfo GetLeaveComment(long leaveId)
        {
            LeaveCommentInfo info = null;
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_LeaveComments WHERE LeaveId=@LeaveId;");
            this.database.AddInParameter(sqlStringCommand, "LeaveId", DbType.Int64, leaveId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateLeaveComment(reader);
                }
            }
            return info;
        }

        public DbQueryResult GetLeaveComments(LeaveCommentQuery query)
        {
            DbQueryResult result = new DbQueryResult();
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_LeaveComments_Get");
            this.database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
            this.database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
            this.database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
            this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, this.BuildLeaveCommentQuery(query));
            this.database.AddOutParameter(storedProcCommand, "Total", DbType.Int32, 4);
            DataSet set = this.database.ExecuteDataSet(storedProcCommand);
            set.Relations.Add("LeaveCommentReplays", set.Tables[0].Columns["LeaveId"], set.Tables[1].Columns["LeaveId"], false);
            result.Data = set;
            result.TotalRecords = (int) this.database.GetParameterValue(storedProcCommand, "Total");
            return result;
        }

        public DataTable GetReplyLeaveComments(long leaveId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_LeaveCommentReplys WHERE LeaveId=@LeaveId");
            this.database.AddInParameter(sqlStringCommand, "LeaveId", DbType.Int64, leaveId);
            DataTable table = new DataTable();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public bool InsertLeaveComment(LeaveCommentInfo leave)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("Insert into Hishop_LeaveComments(UserId,UserName,Title,PublishContent,PublishDate,LastDate)  values(@UserId,@UserName,@Title,@PublishContent,@PublishDate,@LastDate)");
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, leave.UserId);
            this.database.AddInParameter(sqlStringCommand, "UserName", DbType.String, leave.UserName);
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, leave.Title);
            this.database.AddInParameter(sqlStringCommand, "PublishContent", DbType.String, leave.PublishContent);
            this.database.AddInParameter(sqlStringCommand, "PublishDate", DbType.DateTime, DataHelper.GetSafeDateTimeFormat(DateTime.Now));
            this.database.AddInParameter(sqlStringCommand, "LastDate", DbType.DateTime, DataHelper.GetSafeDateTimeFormat(DateTime.Now));
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public int ReplyLeaveComment(LeaveCommentReplyInfo leaveReply)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_LeaveCommentReplys(LeaveId,UserId,ReplyContent,ReplyDate) VALUES(@LeaveId,@UserId,@ReplyContent,@ReplyDate);SELECT @@IDENTITY ");
            this.database.AddInParameter(sqlStringCommand, "leaveId", DbType.Int64, leaveReply.LeaveId);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, leaveReply.UserId);
            this.database.AddInParameter(sqlStringCommand, "ReplyContent", DbType.String, leaveReply.ReplyContent);
            this.database.AddInParameter(sqlStringCommand, "ReplyDate", DbType.String, DataHelper.GetSafeDateTimeFormat(leaveReply.ReplyDate));
            object obj2 = this.database.ExecuteScalar(sqlStringCommand);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return 0;
        }
    }
}

